package com.begamer.card.common.dao.hibernate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.begamer.card.log.DbErrorLogger;
import com.begamer.card.model.pojo.Gift;
import com.begamer.card.model.pojo.Key;

public class CommDao extends HibernateDaoSupport
{
	private static final Logger dbLogger=DbErrorLogger.logger;
	//private static final Logger logger=Logger.getLogger(CommDao.class);
	
	@SuppressWarnings("unchecked")
	public List<Gift> getGifts()
	{
		Session session=getSession();
		try
		{
			String hql="from Gift";
			Query query=session.createQuery(hql);
			List<Gift> gifts=query.list();
			if(gifts!=null && gifts.size()>0)
			{
				String hql2="from Key k where k.giftId=?";
				for(Gift gift:gifts)
				{
					Query query2=session.createQuery(hql2);
					query2.setInteger(0, gift.getId());
					List<Key> keys=query2.list();
					gift.setKeys(keys);
				}
			}
			return gifts;
		}
		catch (Exception e)
		{
			dbLogger.info("查询礼包出错",e);
		}
		finally
		{
			session.close();
		}
		return null;
	}
	
	@SuppressWarnings("deprecation")
	public List<Gift> getGifts(int pageId,int pageSize)
	{
		Session session=getSession();
		try
		{
			List<Gift> gifts=new ArrayList<Gift>();
			String sql="SELECT * from gift LIMIT ?,?";
			Connection conn=session.connection();
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, pageId*pageSize);
			ps.setInt(2, pageSize);
			ResultSet rs=ps.executeQuery();
			while (rs.next())
			{
				gifts.add(new Gift(rs.getInt("ID"), rs.getString("NAME"), rs.getInt("GOLD"), rs.getInt("CRYSTAL"), rs.getInt("RUNENUM"), rs.getInt("POWER"), rs.getString("CARD"), rs.getString("SKILL"), rs.getString("PSKILL"), rs.getString("EQUIP"), rs.getString("ITEM"), rs.getString("STARTDATE"), rs.getString("ENDDATE"), rs.getString("UPDATEDATE"), rs.getInt("NUM"),rs.getInt("FINALNUM")));
			}
			rs.close();
			ps.close();
			conn.close();
			return gifts;
		}
		catch (Exception e)
		{
			dbLogger.info("分页查询礼包出错",e);
		}
		finally
		{
			session.close();
		}
		return null;
	}
	
	public int getGiftCount()
	{
		Session session=getSession();
		try
		{
			String hql="from Gift";
			Query query=session.createQuery(hql);
			return query.list().size();
		}
		catch (Exception e)
		{
			dbLogger.info("查询礼包个数出错",e);
		}
		finally
		{
			session.close();
		}
		return 0;
	}
	
	@SuppressWarnings("unchecked")
	public Gift getGift(int id)
	{
		Session session=getSession();
		try
		{
			String hql="from Gift g where g.id=?";
			Query query=session.createQuery(hql);
			query.setInteger(0, id);
			List<Gift> gifts=query.list();
			if(gifts!=null && gifts.size()>0)
			{
				return gifts.get(0);
			}
		}
		catch (Exception e)
		{
			dbLogger.info("查询礼包出错",e);
		}
		finally
		{
			session.close();
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<Key> getKeys(int giftId)
	{
		Session session=getSession();
		try
		{
			String hql="from Key k where k.giftId=?";
			Query query=session.createQuery(hql);
			query.setInteger(0, giftId);
			return query.list();
		}
		catch (Exception e)
		{
			dbLogger.info("查询激活码出错",e);
		}
		finally
		{
			session.close();
		}
		return null;
	}
	
	public void updateKey(Key key)
	{
		Session session=getSession();
		Transaction ts=session.beginTransaction();
		try
		{
			session.update(key);
			ts.commit();
		}
		catch (Exception e)
		{
			ts.rollback();
			dbLogger.info("更新激活码出错",e);
		}
		finally
		{
			session.close();
		}
	}
	
	public void deleteGift(int id)
	{
		Session session=getSession();
		Transaction ts=session.beginTransaction();
		try
		{
			String hql="delete from Gift g where g.id=?";
			Query query=session.createQuery(hql);
			query.setInteger(0, id);
			query.executeUpdate();
			String hql2="delete from Key k where k.giftId=?";
			Query query2=session.createQuery(hql2);
			query2.setInteger(0, id);
			query2.executeUpdate();
			ts.commit();
		}
		catch (Exception e)
		{
			ts.rollback();
			dbLogger.info("删除激活码出错",e);
		}
		finally
		{
			session.close();
		}
	}
	
	public int getMaxKeyId()
	{
		Session session=getSession();
		try
		{
			String hql="select max(k.id) from Key k";
			Query query=session.createQuery(hql);
			Object result=query.uniqueResult();
			if(result==null)
			{
				return 0;
			}
			else
			{
				return (Integer)result;
			}
		}
		catch (Exception e)
		{
			dbLogger.info("查询激活码最大值出错",e);
		}
		finally
		{
			session.close();
		}
		return 0;
	}
	
	public int saveGift(Gift gift)
	{
		Session session=getSession();
		Transaction ts=session.beginTransaction();
		try
		{
			session.save(gift);
			ts.commit();
			return gift.getId();
		}
		catch (Exception e)
		{
			ts.rollback();
			dbLogger.info("保存礼包出错",e);
		}
		finally
		{
			session.close();
		}
		return 0;
	}
	
	public void saveKeys(List<Key> keys)
	{
		if(keys==null || keys.size()==0)
		{
			return;
		}
		Session session=getSession();
		Transaction ts=session.beginTransaction();
		try
		{
			for(Key key:keys)
			{
				session.save(key);
			}
			ts.commit();
		}
		catch (Exception e)
		{
			ts.rollback();
			dbLogger.info("批量保存激活码出错",e);
		}
		finally
		{
			session.close();
		}
	}
	
	public void updateGift(int id,int addNum)
	{
		Session session=getSession();
		Transaction ts=session.beginTransaction();
		try
		{
			String hql="update Gift g set g.num=g.num+? where g.id=?";
			Query query=session.createQuery(hql);
			query.setInteger(0, addNum);
			query.setInteger(1, id);
			query.executeUpdate();
			ts.commit();
		}
		catch (Exception e)
		{
			ts.rollback();
			dbLogger.info("更新礼包激活码数量出错",e);
		}
		finally
		{
			session.close();
		}
	}
	@SuppressWarnings("unchecked")
	public List<Gift> getGiftByGName(String name)
	{
		Session session = getSession();
		try
		{
			String hql = "from Gift g where g.name like '%" + name + "%'";
			Query query = session.createQuery(hql);
			List<Gift> list = query.list();
			return list;
		}
		catch (RuntimeException e)
		{
			throw e;
		}
		finally
		{
			session.close();
		}
	}
	@SuppressWarnings("deprecation")
	public List<Gift> getGiftByGName(int pageId,int pageSize,String name)
	{
		Session session=getSession();
		try
		{
			List<Gift> gifts=new ArrayList<Gift>();
			String sql="SELECT * from gift where NAME like '%" + name + "%' LIMIT ?,?";
			Connection conn=session.connection();
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, pageId*pageSize);
			ps.setInt(2, pageSize);
			ResultSet rs=ps.executeQuery();
			while (rs.next())
			{
				gifts.add(new Gift(rs.getInt("ID"), rs.getString("NAME"), rs.getInt("GOLD"), rs.getInt("CRYSTAL"), rs.getInt("RUNENUM"), rs.getInt("POWER"), rs.getString("CARD"), rs.getString("SKILL"), rs.getString("PSKILL"), rs.getString("EQUIP"), rs.getString("ITEM"), rs.getString("STARTDATE"), rs.getString("ENDDATE"), rs.getString("UPDATEDATE"), rs.getInt("NUM"),rs.getInt("FINALNUM")));
			}
			rs.close();
			ps.close();
			conn.close();
			return gifts;
		}
		catch (Exception e)
		{
			dbLogger.info("分页查询礼包出错",e);
		}
		finally
		{
			session.close();
		}
		return null;
	}
	@SuppressWarnings("unchecked")
	public int getKeyUseNum()
	{
		try
		{
			List<Key> keys = getHibernateTemplate().find("from Key k where k.status=1");
			return keys.size();
		}
		catch (RuntimeException e)
		{
			dbLogger.info("查询激活码使用量出错",e);
			throw e;
		}
	}
	public void updateGiftUseNum(int id,int num)
	{
		Session session = getSession();
		try
		{
			String sql = "update Gift g set g.finalNum=? where g.id=?";
			Query query = session.createQuery(sql);
			query.setInteger(0, num);
			query.setInteger(1, id);
			query.executeUpdate();
		}
		catch (RuntimeException e)
		{
			dbLogger.info("修改激活码使用量出错",e);
			throw e;
		}
		finally
		{
			session.close();
		}
	}
}
